class MeasurementsTag < ActiveRecord::Base
  attr_accessible :begin_id, :context, :created_at, :end_id, :tag_name, :track, :user

  belongs_to  :track
  belongs_to :user


  def self.tag_counts(query)
    MeasurementsTag.all(query)
  end
  def self.tag_counts_on(context, query)
    unless context.blank?
      query[:conditions].blank? ?
          query[:conditions] = "context like '#{context}%'" :
          query[:conditions] = (query[:conditions] << " and " << "context like '#{context}%' ")
    end
    self.tag_counts(query)
  end

  def self.tag_counts_by_name(where)
    MeasurementsTag.all(
        :select => "count(id), tag_name, measurements_tags.id",
        :group => 'measurements_tags.id, tag_name',
        :where => where)
    end

  def self.tag_counts_by_context(context)
    #MeasurementsTag.all(
    #    :select => "count(id), tag_name, measurements_tags.id",
    #    :group => 'measurements_tags.id, tag_name',
    #    :conditions => "context = '#{context}'")
    MeasurementsTag.all(
        :select => "count(*) , tag_name",
        :group => 'tag_name having count(*) > 50',
        :conditions => "context = '#{context}'",
        :limit => 15)
  end

  def self.tags_by_tracks(trackids)
    MeasurementsTag.where('track_id in (:ids)', :ids => trackids).limit(20)
  end

  def self.tags_for_measure(mid, limit=10)
    # get tags between range, or with begin id if end id == -1
    MeasurementsTag.select('id, tag_name').where(' begin_id = (:id) ', :id => mid).group('id, tag_name')
    #MeasurementsTag.select('id, tag_name').where(' (begin_id = (:id) AND end_id = -1) OR (begin_id >= (:id) AND end_id <> -1 AND end_id <= (:id)) ', :id => mid).group('id, tag_name')  #.having("count(tag_name) < #{limit}")  <- for tagged intervals and deprecated tags
  end

  #geo-locates the tag
  def geolocate
    #find the locaion based on where it started
    mymeasurestarts = Measure.find(self.begin_id)
    mymeasure = Measure.find(self.begin_id)
    location = get_location(mymeasure)

    location
  end

  #method first_geom - returns the first geometry associated with the begin_id
  def first_geom
    measures = self.end_id == -1 ?  Measure.where("id =#{self.begin_id} AND geom IS NOT NULL")
        :  Measure.where("id between #{self.begin_id} AND #{self.end_id} AND geom IS NOT NULL")
    measures.first.try(:geom)
  end

  # measures - gets all the measures for this tag
  def measure
    self.end_id == -1 ? Measure.where(" measures.id = #{self.begin_id}" ).all  :
                      Measure.where(" id BETWEEN #{self.begin_id} AND #{self.end_id}").all
  end

  #the number of measures that this tag represents
  def measures_count
     1 if self.end_id == -1
    end_id - begin_id
  end

  # measures - gets all the measures for this tag that are geo-located
  def geo_measures
    Measure.where(" ( (measures.id = #{self.begin_id} AND measures.end_id = -1) OR (measures.id BETWEEN  #{self.begin_id} AND #{self.end_id}) ) AND measures.geom IS NOT NULL")
  end

  def self.save_tags_as_transactions(sql_start, sql_values)
    ids = []
    sql = sql_start << sql_values.join(",") << " RETURNING id"
    self.transaction do
      result = ActiveRecord::Base.connection.execute sql
      #return ids as array
      result.each do |row|
        ids << row["id"] unless row["id"].nil?
      end
    end
    ids
  end


end
